package dao.impl;

import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import dao.BookDao;
import model.Book;
import model.BookReview;
import model.BorrowBook;
import model.Reading;
import util.JDBCUtiles;

public class BookDaoImpl implements BookDao {

	@Override
	public List queryAllBook() throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT book.ISBN,booktype.TypeName,book.BookName,book.Author,book.Publish,book.UnitPrice,book.Count "
				+ "FROM book,booktype "
				+ "WHERE book.TypeID=booktype.TypeID" ;
		return qr.query(sql, new BeanListHandler<Book>(Book.class));
	}

	@Override
	public boolean bookingById(String uid , String bid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "INSERT INTO borrowbook VALUES (?,?,?,?,'待审核')";
		Date date = new Date();
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		calendar.add(calendar.DAY_OF_MONTH, 20);
		Date backTime = calendar.getTime();
		int i = qr.update(sql, uid,bid,date,backTime);
		return  i > 0 ? true : false;
	}

	@Override
	public List queryBookByName(String name) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT book.ISBN,booktype.TypeName,book.BookName,book.Author,book.Publish,book.UnitPrice,book.Count "
				+ "FROM book,booktype "
				+ "WHERE book.TypeID=booktype.TypeID AND book.BookName=?" ;
		return qr.query(sql, new BeanListHandler<Book>(Book.class),name);
	}

	@Override
	public List queryBorrowById(String uid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT borrowbook.ISBN,booktype.TypeName,book.BookName,book.Author,book.Publish,book.UnitPrice,book.Count,borrowbook.State "
				+ "FROM book,borrowbook,booktype "
				+ "WHERE book.ISBN = borrowbook.ISBN AND book.TypeID = booktype.TypeID AND borrowbook.ReaderID=?" ;
		return qr.query(sql, new BeanListHandler<BorrowBook>(BorrowBook.class),uid);
	}

	@Override
	public List queryReadingListByid(String uid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "SELECT borrowbook.ISBN,book.BookName,book.Author,borrowbook.BorrowDate,borrowbook.ReturnDate "
				+ "FROM book,borrowbook "
				+ "WHERE book.ISBN=borrowbook.ISBN AND borrowbook.ReaderID=? AND State='通过'" ;
		return qr.query(sql, new BeanListHandler<Reading>(Reading.class),uid);
	}

	@Override
	public boolean renewById(String uid, String bid) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql  = "UPDATE borrowbook SET ReturnDate=? WHERE ReaderID=? AND ISBN=?";
		Date date = new Date();
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		calendar.add(calendar.DAY_OF_MONTH, 20);
		Date backTime = calendar.getTime();
		int i = qr.update(sql,backTime,uid,bid);
		return  i > 0 ? true : false;
	}

	@Override
	public List searchReview(String name) throws SQLException {
		//得到QueryRunner对象
		QueryRunner qr = JDBCUtiles.getQueryRunner();
		//sql语句
		String sql = "select reader.ReaderName,review.ISBN,book.BookName,review.Grade,review.Talk "
				+ "FROM reader,review,book "
				+ "WHERE reader.ReaderID=review.ReaderID AND review.ISBN=book.ISBN AND book.BookName=?" ;
		return qr.query(sql, new BeanListHandler<BookReview>(BookReview.class),name);
	}

}
